
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 03/17/2016 09:50:59
-- Generated from EDMX file: E:\MVC Project\ChurchPro\ChurchEdu\DataModel\Model1.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [Church_Data];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Comment_Resource]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Comment] DROP CONSTRAINT [FK_Comment_Resource];
GO
IF OBJECT_ID(N'[dbo].[FK_Comment_UserProfile]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Comment] DROP CONSTRAINT [FK_Comment_UserProfile];
GO
IF OBJECT_ID(N'[dbo].[FK_Resource_Directory]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Resource] DROP CONSTRAINT [FK_Resource_Directory];
GO
IF OBJECT_ID(N'[dbo].[fk_RoleId]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[webpages_UsersInRoles] DROP CONSTRAINT [fk_RoleId];
GO
IF OBJECT_ID(N'[dbo].[FK_UserGrade_Resource]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserGrade] DROP CONSTRAINT [FK_UserGrade_Resource];
GO
IF OBJECT_ID(N'[dbo].[FK_UserGrade_UserProfile]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserGrade] DROP CONSTRAINT [FK_UserGrade_UserProfile];
GO
IF OBJECT_ID(N'[dbo].[fk_UserId]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[webpages_UsersInRoles] DROP CONSTRAINT [fk_UserId];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Comment]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Comment];
GO
IF OBJECT_ID(N'[dbo].[Directory]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Directory];
GO
IF OBJECT_ID(N'[dbo].[Resource]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Resource];
GO
IF OBJECT_ID(N'[dbo].[UserGrade]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserGrade];
GO
IF OBJECT_ID(N'[dbo].[UserProfile]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserProfile];
GO
IF OBJECT_ID(N'[dbo].[webpages_Membership]', 'U') IS NOT NULL
    DROP TABLE [dbo].[webpages_Membership];
GO
IF OBJECT_ID(N'[dbo].[webpages_OAuthMembership]', 'U') IS NOT NULL
    DROP TABLE [dbo].[webpages_OAuthMembership];
GO
IF OBJECT_ID(N'[dbo].[webpages_Roles]', 'U') IS NOT NULL
    DROP TABLE [dbo].[webpages_Roles];
GO
IF OBJECT_ID(N'[dbo].[webpages_UsersInRoles]', 'U') IS NOT NULL
    DROP TABLE [dbo].[webpages_UsersInRoles];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Comment'
CREATE TABLE [dbo].[Comment] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Com_Content] varchar(max)  NULL,
    [TimeStamp] datetime  NULL,
    [ParentID] int  NOT NULL,
    [User_ID] int  NOT NULL,
    [Res_ID] int  NOT NULL
);
GO

-- Creating table 'Directory'
CREATE TABLE [dbo].[Directory] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Dire_Name] nvarchar(50)  NOT NULL,
    [Parent_ID] int  NOT NULL
);
GO

-- Creating table 'Resource'
CREATE TABLE [dbo].[Resource] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Res_Name] nvarchar(50)  NOT NULL,
    [Res_Url] nvarchar(50)  NULL,
    [Dire_ID] int  NOT NULL
);
GO

-- Creating table 'UserGrade'
CREATE TABLE [dbo].[UserGrade] (
    [Res_ID] int  NOT NULL,
    [User_ID] int  NOT NULL,
    [GradeLevel] int  NULL
);
GO

-- Creating table 'UserProfile'
CREATE TABLE [dbo].[UserProfile] (
    [UserId] int IDENTITY(1,1) NOT NULL,
    [UserName] nvarchar(56)  NOT NULL
);
GO

-- Creating table 'webpages_Membership'
CREATE TABLE [dbo].[webpages_Membership] (
    [UserId] int  NOT NULL,
    [CreateDate] datetime  NULL,
    [ConfirmationToken] nvarchar(128)  NULL,
    [IsConfirmed] bit  NULL,
    [LastPasswordFailureDate] datetime  NULL,
    [PasswordFailuresSinceLastSuccess] int  NOT NULL,
    [Password] nvarchar(128)  NOT NULL,
    [PasswordChangedDate] datetime  NULL,
    [PasswordSalt] nvarchar(128)  NOT NULL,
    [PasswordVerificationToken] nvarchar(128)  NULL,
    [PasswordVerificationTokenExpirationDate] datetime  NULL
);
GO

-- Creating table 'webpages_OAuthMembership'
CREATE TABLE [dbo].[webpages_OAuthMembership] (
    [Provider] nvarchar(30)  NOT NULL,
    [ProviderUserId] nvarchar(100)  NOT NULL,
    [UserId] int  NOT NULL
);
GO

-- Creating table 'webpages_Roles'
CREATE TABLE [dbo].[webpages_Roles] (
    [RoleId] int IDENTITY(1,1) NOT NULL,
    [RoleName] nvarchar(256)  NOT NULL
);
GO

-- Creating table 'webpages_UsersInRoles'
CREATE TABLE [dbo].[webpages_UsersInRoles] (
    [UserId] int  NOT NULL,
    [RoleId] int  NOT NULL,
    [ID] int IDENTITY(1,1) NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID] in table 'Comment'
ALTER TABLE [dbo].[Comment]
ADD CONSTRAINT [PK_Comment]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Directory'
ALTER TABLE [dbo].[Directory]
ADD CONSTRAINT [PK_Directory]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Resource'
ALTER TABLE [dbo].[Resource]
ADD CONSTRAINT [PK_Resource]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [Res_ID], [User_ID] in table 'UserGrade'
ALTER TABLE [dbo].[UserGrade]
ADD CONSTRAINT [PK_UserGrade]
    PRIMARY KEY CLUSTERED ([Res_ID], [User_ID] ASC);
GO

-- Creating primary key on [UserId] in table 'UserProfile'
ALTER TABLE [dbo].[UserProfile]
ADD CONSTRAINT [PK_UserProfile]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- Creating primary key on [UserId] in table 'webpages_Membership'
ALTER TABLE [dbo].[webpages_Membership]
ADD CONSTRAINT [PK_webpages_Membership]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- Creating primary key on [Provider], [ProviderUserId] in table 'webpages_OAuthMembership'
ALTER TABLE [dbo].[webpages_OAuthMembership]
ADD CONSTRAINT [PK_webpages_OAuthMembership]
    PRIMARY KEY CLUSTERED ([Provider], [ProviderUserId] ASC);
GO

-- Creating primary key on [RoleId] in table 'webpages_Roles'
ALTER TABLE [dbo].[webpages_Roles]
ADD CONSTRAINT [PK_webpages_Roles]
    PRIMARY KEY CLUSTERED ([RoleId] ASC);
GO

-- Creating primary key on [ID] in table 'webpages_UsersInRoles'
ALTER TABLE [dbo].[webpages_UsersInRoles]
ADD CONSTRAINT [PK_webpages_UsersInRoles]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Res_ID] in table 'Comment'
ALTER TABLE [dbo].[Comment]
ADD CONSTRAINT [FK_Comment_Resource]
    FOREIGN KEY ([Res_ID])
    REFERENCES [dbo].[Resource]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Comment_Resource'
CREATE INDEX [IX_FK_Comment_Resource]
ON [dbo].[Comment]
    ([Res_ID]);
GO

-- Creating foreign key on [User_ID] in table 'Comment'
ALTER TABLE [dbo].[Comment]
ADD CONSTRAINT [FK_Comment_UserProfile]
    FOREIGN KEY ([User_ID])
    REFERENCES [dbo].[UserProfile]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Comment_UserProfile'
CREATE INDEX [IX_FK_Comment_UserProfile]
ON [dbo].[Comment]
    ([User_ID]);
GO

-- Creating foreign key on [Dire_ID] in table 'Resource'
ALTER TABLE [dbo].[Resource]
ADD CONSTRAINT [FK_Resource_Directory]
    FOREIGN KEY ([Dire_ID])
    REFERENCES [dbo].[Directory]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Resource_Directory'
CREATE INDEX [IX_FK_Resource_Directory]
ON [dbo].[Resource]
    ([Dire_ID]);
GO

-- Creating foreign key on [Res_ID] in table 'UserGrade'
ALTER TABLE [dbo].[UserGrade]
ADD CONSTRAINT [FK_UserGrade_Resource]
    FOREIGN KEY ([Res_ID])
    REFERENCES [dbo].[Resource]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [User_ID] in table 'UserGrade'
ALTER TABLE [dbo].[UserGrade]
ADD CONSTRAINT [FK_UserGrade_UserProfile]
    FOREIGN KEY ([User_ID])
    REFERENCES [dbo].[UserProfile]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserGrade_UserProfile'
CREATE INDEX [IX_FK_UserGrade_UserProfile]
ON [dbo].[UserGrade]
    ([User_ID]);
GO

-- Creating foreign key on [UserId] in table 'webpages_UsersInRoles'
ALTER TABLE [dbo].[webpages_UsersInRoles]
ADD CONSTRAINT [fk_UserId]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[UserProfile]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'fk_UserId'
CREATE INDEX [IX_fk_UserId]
ON [dbo].[webpages_UsersInRoles]
    ([UserId]);
GO

-- Creating foreign key on [RoleId] in table 'webpages_UsersInRoles'
ALTER TABLE [dbo].[webpages_UsersInRoles]
ADD CONSTRAINT [fk_RoleId]
    FOREIGN KEY ([RoleId])
    REFERENCES [dbo].[webpages_Roles]
        ([RoleId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'fk_RoleId'
CREATE INDEX [IX_fk_RoleId]
ON [dbo].[webpages_UsersInRoles]
    ([RoleId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------